﻿using Mysoft.Map.Extensions.DAL;
using Mysoft.QuickCode.DAL.Tool;
using Mysoft.QuickCode.DAL.Util;
using Mysoft.QuickCode.DbExpressionExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace Mysoft.QuickCode.DAL
{
    /// <summary>
    /// 表信息
    /// </summary>
    class TableInfo
    {
        public string TableName { set; get; }
        public string PrimaryKeyName { set; get; }
        public PropertyInfo PrimaryKeyProperty { set; get; }
    }

    /// <summary>
    /// 不明确数据类型数据库访问对象。内置基本的CRUD方法。
    /// </summary>
    public sealed class BaseDAL
    {
        /// <summary>
        /// 获取表信息：表名，主键
        /// </summary>
        /// <returns></returns>
        TableInfo GetTableInfo<T>(T entity)
        {
            Type type = entity.GetType();
            //表名
            DataEntityAttribute entityAttr = type.GetCustomAttributes(false).FirstOrDefault(m => m is DataEntityAttribute) as DataEntityAttribute;
            string tbName = entityAttr == null ? type.Name : entityAttr.Alias;    //没有定义别名的情况下，用类名做表名

            //主键名
            //PropertyInfo[] ps = type.GetProperties();
            //DataColumnAttribute a = ps[0].GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute;
            PropertyInfo propPrimaryKey = type.GetProperties()
                .FirstOrDefault(
                    m => ((m.GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute).PrimaryKey == true)
                ) as PropertyInfo;
            if (propPrimaryKey == null)
            {
                throw new Exception(string.Format("对象{0}未找到属性 PrimaryKey=true 的主键！", type.Name));
            }

            return new TableInfo
            {
                TableName = tbName,
                PrimaryKeyName = propPrimaryKey.Name,
                PrimaryKeyProperty = propPrimaryKey
            };
        }

        ///// <summary>
        ///// 保存实体
        ///// </summary>
        ///// <param name="entity">要新增或更新的对象，如果是BaseEntity子类。则字段默认值不会引发更新，请使用小平台的SetDefaultValue或调用此方法有重载参数updateDefaultValue的方法。</param>
        ///// <returns></returns>
        //public object SaveEntity(object entity)
        //{
        //    if (entity is BaseEntity)
        //    {
        //        return SaveEntity<BaseEntity>(entity as BaseEntity);
        //    }
        //    else if (BaseEntityPlusUtil.IsInstance(entity))
        //    {
        //        return SaveEntity<BaseEntityPlus>(entity as BaseEntityPlus);
        //    }
        //    else
        //    {
        //        throw new NotSupportedException("类型必须为BaseEntity或BaseEntityPlus的子类！");
        //    }
        //}

        ///// <summary>
        ///// 保存实体
        ///// </summary>
        ///// <param name="entity">要新增或更新的对象</param>
        ///// <param name="updateDefaultValue">在更新模式下自动更新默认值字段，因为小平台默认值字段在Update的时候不会更新，如果传true，则在Update的时候会自动处理默认值字段，请注意此场景下应该先从数据库获取最新数据再处理</param>
        ///// <returns></returns>
        //public object SaveEntity(object entity, bool updateDefaultValue)
        //{
        //    if (entity is BaseEntity)
        //    {
        //        return SaveEntity<BaseEntity>(entity as BaseEntity, updateDefaultValue);
        //    }
        //    else if (entity is BaseEntityPlus)
        //    {
        //        return SaveEntity<BaseEntityPlus>(entity as BaseEntityPlus);
        //    }
        //    else
        //    {
        //        throw new NotSupportedException("类型必须为BaseEntity或BaseEntityPlus的子类！");
        //    }
        //}

        /// <summary>
        /// 删除实体对象
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int DeleteEntity(object entity)
        {
            if (entity is BaseEntity)
            {
                return (entity as BaseEntity).Delete();
            }
            else if (BaseEntityPlusUtil.IsInstance(entity))
            {
                BaseEntityPlusUtil.Delete(entity);  //标记为删除
                return BaseEntityPlusUtil.SyncDB(entity) ? 1 : 0;
            }
            else
            {
                throw new NotSupportedException("类型必须为BaseEntity或BaseEntityPlus的子类！");
            }
        }

        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要新增或更新的对象</param>
        /// <param name="updateDefaultValue">在更新模式下自动更新默认值字段，因为小平台默认值字段在Update的时候不会更新，如果传true，则在Update的时候会自动处理默认值字段，请注意此场景下应该先从数据库获取最新数据再处理</param>
        /// <returns>主键ID</returns>
        public object SaveEntity<T>(T entity, bool updateDefaultValue)
        {
            TableInfo ti = this.GetTableInfo(entity);
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            PropertyInfo pi = entity.GetType().GetProperty(ti.PrimaryKeyName);
            object id = pi.GetValue(entity, null);    //获取主键值
            if (CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1)
            {
                //如果存在此记录，则执行更新
                if (entity is BaseEntity)
                {
                    var be = (entity as BaseEntity);
                    if (updateDefaultValue == true)
                    {
                        BaseEntityUtil.SetDefaultValues(be);
                    }
                    be.Update();
                }
                else if (BaseEntityPlusUtil.IsInstance(entity))
                {
                    BaseEntityPlusUtil.SyncDB(entity);
                }
            }
            else
            {
                //如此不存在此记录，则新增

                //如果主键是GUID，且没有赋值，则帮GUID赋默认值
                if (pi.PropertyType == typeof(Guid) && Guid.Empty.Equals(id))
                {
                    pi.SetValue(entity, Guid.NewGuid(), null);
                }
                if (entity is BaseEntity)
                {
                    var be = (entity as BaseEntity);
                    if (updateDefaultValue == true)
                    {
                        BaseEntityUtil.SetDefaultValues(be);
                    }
                    be.Insert();
                }
                else if (BaseEntityPlusUtil.IsInstance(entity))
                {
                    BaseEntityPlusUtil.SyncDB(entity);
                }
            }

            return pi.GetValue(entity, null);
        }

        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <param name="entity">要新增或更新的对象，如果是BaseEntity子类。则字段默认值不会引发更新，请使用小平台的SetDefaultValue或调用此方法有重载参数updateDefaultValue的方法。</param>
        /// <returns>主键ID</returns>
        public object SaveEntity<T>(T entity)
        {
            return SaveEntity<T>(entity, false);
        }

        /// <summary>
        /// 删除实体对应的表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">实体主键</param>
        /// <param name="objectInstance">实体对象实例。会通过反射获取对应的表映射信息。</param>
        public int DeleteEntity<T>(object id, T objectInstance)
        {
            string sql = "DELETE FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo(objectInstance);
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 泛型数据类型数据库访问层。内置多种基础数据库访问方法，代替SQL编码。
    /// </summary>
    /// <typeparam name="T">BaseEntity或BaseEntityPlus实体</typeparam>
    public sealed class BaseDAL<T>
        where T : class, new()
    {
        /// <summary>
        /// 获取表信息：表名，主键
        /// </summary>
        /// <returns></returns>
        TableInfo GetTableInfo()
        {
            Type type = typeof(T);
            //表名
            DataEntityAttribute entityAttr = type.GetCustomAttributes(false).FirstOrDefault(m => m is DataEntityAttribute) as DataEntityAttribute;
            string tbName = entityAttr == null ? type.Name : entityAttr.Alias;    //没有定义别名的情况下，用类名做表名

            //主键名
            //PropertyInfo[] ps = type.GetProperties();
            //DataColumnAttribute a = ps[0].GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute;
            PropertyInfo propPrimaryKey = type.GetProperties()
                .FirstOrDefault(
                    m => ((m.GetCustomAttributes(false).FirstOrDefault(c => c is DataColumnAttribute) as DataColumnAttribute).PrimaryKey == true)
                ) as PropertyInfo;
            if (propPrimaryKey == null)
            {
                throw new Exception(string.Format("对象{0}未定义属性 PrimaryKey=true 的主键！", type.Name));
            }

            return new TableInfo
            {
                TableName = tbName,
                PrimaryKeyName = propPrimaryKey.Name
            };
        }

        /// <summary>
        /// 从数据库中获取数据并转换为对应的实体
        /// </summary>
        /// <param name="id">数据库中的主键</param>
        /// <returns></returns>
        public T GetEntity(object id)
        {
            string sql = "SELECT TOP 1 * FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ToSingle<T>();
        }

        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <param name="entity">要新增或更新的对象</param>
        /// <param name="updateDefaultValue">在更新模式下自动更新默认值字段，因为小平台默认值字段在Update的时候不会更新，如果传true，则在Update的时候会自动处理默认值字段，请注意此场景下应该先从数据库获取最新数据再处理</param>
        /// <returns>主键ID</returns>
        public T SaveEntity(T entity, bool updateDefaultValue)
        {
            TableInfo ti = this.GetTableInfo();
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            PropertyInfo pi = typeof(T).GetProperty(ti.PrimaryKeyName);
            object id = pi.GetValue(entity, null);    //获取主键值
            if (CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1)
            {
                //如果存在此记录，则执行更新
                if (entity is BaseEntity)
                {
                    var be = (entity as BaseEntity);
                    if (updateDefaultValue == true)
                    {
                        BaseEntityUtil.SetDefaultValues(be);
                    }
                    be.Update();
                }
                else if (BaseEntityPlusUtil.IsInstance(entity))
                {
                    BaseEntityPlusUtil.SyncDB(entity);
                }
            }
            else
            {
                //如此不存在此记录，则新增

                //如果主键是GUID，且没有赋值，则帮GUID赋默认值
                if (pi.PropertyType == typeof(Guid) && Guid.Empty.Equals(id))
                {
                    pi.SetValue(entity, Guid.NewGuid(), null);
                }
                if (entity is BaseEntity)
                {
                    var be = (entity as BaseEntity);
                    if (updateDefaultValue == true)
                    {
                        BaseEntityUtil.SetDefaultValues(be);
                    }
                    be.Insert();
                }
                else if (BaseEntityPlusUtil.IsInstance(entity))
                {
                    BaseEntityPlusUtil.SyncDB(entity);
                }
            }

            return entity;
        }

        /// <summary>
        /// 新增或更新：如果主键ID值在数据库中已经存在则会执行更新，否则执行新增。
        /// </summary>
        /// <param name="entity">要新增或更新的对象，如果是BaseEntity子类。则字段默认值不会引发更新，请使用小平台的SetDefaultValue或调用此方法有重载参数updateDefaultValue的方法。</param>
        /// <returns></returns>
        public T SaveEntity(T entity)
        {
            return SaveEntity(entity, false);
        }

        /// <summary>
        /// 删除数据库中主键对应的记录
        /// </summary>
        /// <param name="id">数据库中的主键</param>
        /// <returns></returns>
        public int DeleteEntity(object id)
        {
            string sql = "DELETE FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ExecuteNonQuery();
        }

        /// <summary>
        /// 查询ID对应的数据，并填充到DataTable
        /// </summary>
        /// <param name="id">实体数据库主键值</param>
        /// <returns></returns>
        public DataTable GetEntityTable(object id)
        {
            string sql = "SELECT TOP 1 * FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).FillDataTable();
        }

        /// <summary>
        /// 高性能批量插入数据
        /// </summary>
        /// <param name="entityList">批量插入的数据集合</param>
        public void BatchInsert(List<T> entityList)
        {
            new EntityTable<T>().BatchInsert(entityList);
        }

        /// <summary>
        /// 批量删除数据，数据实体必须有一个唯一字段主键
        /// </summary>
        /// <param name="entityList"></param>
        public void BatchDelete(List<T> entityList)
        {
            TableInfo ti = this.GetTableInfo();
            PropertyInfo pi = typeof(T).GetProperty(ti.PrimaryKeyName);
            var sql = $"delete from {ti.TableName} where {ti.PrimaryKeyName} in @idList";
            var idList = new List<object>();
            foreach (var entity in entityList)
            {
                object id = pi.GetValue(entity, null);    //获取主键值
                idList.Add(id);
            }

            if (idList.Count() == 0)
            {
                return;
            }

            CPQuery.From(sql, new { idList = idList }).ExecuteNonQuery();

        }

        /// <summary>
        /// 批量删除数据，数据实体必须有一个唯一字段主键
        /// </summary>
        /// <param name="idList">主键集合</param>
        public void BatchDelete(List<object> idList)
        {
            TableInfo ti = this.GetTableInfo();
            PropertyInfo pi = typeof(T).GetProperty(ti.PrimaryKeyName);
            var sql = $"delete from {ti.TableName} where {ti.PrimaryKeyName} in @idList";

            if (idList.Count() == 0)
            {
                return;
            }

            CPQuery.From(sql, new { idList = idList }).ExecuteNonQuery();

        }


        /// <summary>
        /// 判断主键对应的记录是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(object id)
        {
            string sql = "SELECT TOP 1 1 FROM {0} WHERE {1}=@ID";
            TableInfo ti = this.GetTableInfo();
            sql = string.Format(sql, ti.TableName, ti.PrimaryKeyName);
            return CPQuery.From(sql, new { ID = id }).ExecuteScalar<int>() == 1;
        }

        #region Linq方式的CRUD实现的方法


        /// <summary>
        /// Expression方式查询对象，代替原GetEntity的Expression表达式方法。
        /// </summary>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public T GetEntityOne(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expr)
                .AsQuery().ToSingle<T>();
        }

        /// <summary>
        ///  Expression方式查询对象，代替原GetEntity的Expression表达式方法。
        /// </summary>
        /// <param name="expr"></param>
        /// <param name="orderFields"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public T GetEntity(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToSingle<T>();
        }

        /// <summary>
        /// 条件查询实体列表
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<T> GetEntityList()
        {
            return EntityTable<T>.Instance
                .Select()
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <param name="orderFields">排序字段</param>
        /// <param name="order">排序方式</param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <param name="orderFields"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public List<T> GetEntityList(Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }


        /// <summary>
        /// 按条件查询数据，并填充到DataTable
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public DataTable GetEntityTable(Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .AsQuery().FillDataTable();
        }

        /// <summary>
        /// 查询所有数据到DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable GetEntityTable()
        {
            return EntityTable<T>.Instance
                .Select()
                .AsQuery().FillDataTable();
        }

        /// <summary>
        /// 数据查询到DataTalbe集合
        /// </summary>
        /// <param name="expr"></param>
        /// <param name="orderFields"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public DataTable GetEntityTable(Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().FillDataTable();
        }

        /// <summary>
        /// Expression方式更新
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expUpdate">更新字段</param>
        /// <param name="expWhere">更新条件</param>
        /// <returns></returns>
        public void UpdateEntity(Expression<Func<T, T>> expUpdate, Expression<Func<T, bool>> expWhere)
        {
            EntityTable<T>.Instance
                .Update(expUpdate)
                .Where(expWhere)
                .AsQuery().ExecuteNonQuery();
        }

        /// <summary>
        /// Expression方式批量删除对象
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expWhere">删除条件</param>
        public void DeleteEntities(Expression<Func<T, bool>> expWhere)
        {
            EntityTable<T>.Instance
                .Delete()
                .Where(expWhere)
                .AsQuery().ExecuteNonQuery();
        }

        /// <summary>
        /// 判断主键对应的记录是否存在
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="expWhere">查询条件</param>
        /// <returns></returns>
        public bool ExistsAny(Expression<Func<T, bool>> expWhere)
        {
            return EntityTable<T>.Instance
                .Select()
                .Top(1)
                .Where(expWhere)
                .AsQuery().FillDataTable().Rows.Count > 0;
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="fields">查询字段</param>
        /// <param name="expr">查询条件Expression</param>
        /// <returns></returns>
        public List<T> Distinct(Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr)
        {
            return EntityTable<T>.Instance
                .Select(fields)
                .Distinct()
                .Where(expr)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 查询实体集合，并排序
        /// </summary>
        /// <typeparam name="T">BaseEntity类型对象</typeparam>
        /// <param name="fields">查询字段</param>
        /// <param name="expr">查询条件Expression</param>
        /// <param name="orderFields">排序字段</param>
        /// <param name="order">排序方式</param>
        /// <returns></returns>
        public List<T> Distinct(Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr, Expression<Func<T, object[]>> orderFields, OrderByType order)
        {
            return EntityTable<T>.Instance
                .Select(fields)
                .Distinct()
                .Where(expr)
                .OrderBy(orderFields, order)
                .AsQuery().ToList<T>();
        }

        /// <summary>
        /// 聚合函数方法
        /// </summary>
        /// <typeparam name="M"></typeparam>
        /// <param name="method">聚合方法名称</param>
        /// <param name="fields"></param>
        /// <param name="expr"></param>
        /// <returns></returns>
        public M Aggregate<M>(Aggregate method, Expression<Func<T, object[]>> fields, Expression<Func<T, bool>> expr)
        {
            EntityTable<T> entityTable = EntityTable<T>.Instance;
            List<string> paraList = EntityTableExtensions.ParseFieldList(entityTable, fields);
            entityTable.CommandText.Insert(0, string.Format("SELECT {0}({1}) FROM {2} ",
                method.ToString(),
                string.Join(", ", paraList.ToArray()),
                entityTable.GetTableName()));
            return entityTable.AsQuery().ExecuteScalar<M>();
        }

        #endregion

        #region 下面的几个一定要放在最下面，千万别换位置，因为VB类库无法识别object和express表达式，导致匹配函数出错

        /// <summary>
        /// C#可正常使用。如果是VB.NET编程，且只需要Expression查询请调用GetEntityOne方法，因为VB无法识别object和Expression参数类型。
        /// </summary>
        /// <param name="expr"></param>
        /// <returns></returns>
        public T GetEntity(Expression<Func<T, bool>> expr)
        {
            return GetEntityOne(expr);
        }

        /// <summary>
        /// C#可正常使用。如果是VB.NET编程，且只需要Expression查询请调用ExistsAny方法，因为VB无法识别object和Expression参数类型。
        /// </summary>
        /// <param name="expWhere"></param>
        /// <returns></returns>
        public bool Exists(Expression<Func<T, bool>> expWhere)
        {
            return ExistsAny(expWhere);
        }

        #endregion
    }
}
